#!/usr/bin/env python3
"""
市场数据管理模块
提供市场资本化、交易量和货币转换功能
"""

import asyncio
import logging
import aiohttp
from typing import Dict, List, Optional, Any
from dataclasses import dataclass, asdict
from datetime import datetime, timedelta
from contextlib import asynccontextmanager

try:
    from .utils import (
        CryptoAgentError, safe_float, safe_int, 
        async_timing_decorator, get_current_timestamp, retry_decorator
    )
except ImportError:
    from utils import (
        CryptoAgentError, safe_float, safe_int, 
        async_timing_decorator, get_current_timestamp, retry_decorator
    )
# We'll initialize the database manager within the class to avoid import issues
db_manager = None

logger = logging.getLogger(__name__)


class MarketDataError(CryptoAgentError):
    """市场数据专用异常"""
    def __init__(self, message: str, symbol: str = None, source: str = None):
        super().__init__(message, "MARKET_DATA_ERROR", {
            'symbol': symbol,
            'source': source
        })


class CurrencyConversionError(CryptoAgentError):
    """货币转换专用异常"""
    def __init__(self, message: str, from_currency: str = None, to_currency: str = None, source: str = None):
        super().__init__(message, "CURRENCY_CONVERSION_ERROR", {
            'from_currency': from_currency,
            'to_currency': to_currency,
            'source': source
        })


@dataclass
class MarketData:
    """市场数据模型"""
    symbol: str
    market_cap_usd: float
    market_cap_rank: int
    volume_24h_usd: float
    volume_change_24h_percent: float
    last_updated: datetime
    
    def to_dict(self) -> Dict[str, Any]:
        """转换为字典格式"""
        return asdict(self)
    
    def to_multi_currency(self, rates: Dict[str, float]) -> Dict[str, Dict[str, float]]:
        """转换为多货币格式"""
        result = {}
        
        # 市值转换
        market_cap = {'USD': self.market_cap_usd}
        for currency, rate in rates.items():
            if currency != 'USD':
                market_cap[currency] = self.market_cap_usd * rate
        result['market_cap'] = market_cap
        
        # 交易量转换
        volume = {'USD': self.volume_24h_usd}
        for currency, rate in rates.items():
            if currency != 'USD':
                volume[currency] = self.volume_24h_usd * rate
        result['volume_24h'] = volume
        
        return result


@dataclass
class CurrencyRates:
    """汇率数据模型"""
    base_currency: str
    rates: Dict[str, float]  # {EUR: 0.85, CNY: 7.2}
    timestamp: datetime
    source: str
    
    def to_dict(self) -> Dict[str, Any]:
        """转换为字典格式"""
        return asdict(self)
    
    def get_rate(self, target_currency: str) -> Optional[float]:
        """获取指定货币的汇率"""
        if target_currency == self.base_currency:
            return 1.0
        return self.rates.get(target_currency)
    
    def is_expired(self, ttl_minutes: int = 5) -> bool:
        """检查汇率是否过期"""
        expiry_time = self.timestamp + timedelta(minutes=ttl_minutes)
        return datetime.now() > expiry_time


class MarketDataSource:
    """市场数据源基类"""
    
    def __init__(self, name: str, base_url: str, rate_limit: int = 60):
        self.name = name
        self.base_url = base_url
        self.rate_limit = rate_limit
        self.last_request_time = 0
        self._cache: Dict[str, Dict[str, Any]] = {}
        self.cache_ttl_seconds = 300  # 5分钟缓存
        
    async def get_market_data(self, symbol: str) -> MarketData:
        """获取市场数据 - 子类需要实现"""
        raise NotImplementedError
    
    async def get_exchange_rates(self, base_currency: str, target_currencies: List[str]) -> CurrencyRates:
        """获取汇率数据 - 子类需要实现"""
        raise NotImplementedError
    
    async def _rate_limit_check(self):
        """速率限制检查"""
        current_time = datetime.now().timestamp()
        time_since_last = current_time - self.last_request_time
        min_interval = 60 / self.rate_limit
        
        if time_since_last < min_interval:
            sleep_time = min_interval - time_since_last
            await asyncio.sleep(sleep_time)
        
        self.last_request_time = datetime.now().timestamp()
    
    def _is_cache_valid(self, cache_key: str) -> bool:
        """检查缓存是否有效"""
        if cache_key not in self._cache:
            return False
        
        cache_entry = self._cache[cache_key]
        cache_time = cache_entry.get('timestamp', 0)
        current_time = datetime.now().timestamp()
        
        return (current_time - cache_time) < self.cache_ttl_seconds
    
    def _set_cache(self, cache_key: str, data: Any):
        """设置缓存"""
        self._cache[cache_key] = {
            'data': data,
            'timestamp': datetime.now().timestamp()
        }
    
    def _get_cache(self, cache_key: str) -> Optional[Any]:
        """获取缓存数据"""
        if self._is_cache_valid(cache_key):
            return self._cache[cache_key]['data']
        return None


class CurrencyConverter:
    """货币转换器"""
    
    def __init__(self, cache_ttl_minutes: int = 5):
        """
        初始化货币转换器
        
        Args:
            cache_ttl_minutes: 缓存TTL（分钟），默认5分钟
        """
        self.cache_ttl_minutes = cache_ttl_minutes
        self.supported_currencies = ['USD', 'EUR', 'CNY']
        self._rates_cache: Optional[CurrencyRates] = None
        
        # 初始化数据库管理器
        self._init_db_manager()
        
        # 汇率数据源将在需要时延迟初始化
        self.rate_source = None
    
    def _init_db_manager(self):
        """初始化数据库管理器"""
        global db_manager
        if db_manager is None:
            # 使用简单的数据库管理器用于测试
            import sqlite3
            import os
            from contextlib import contextmanager
            
            class SimpleDBManager:
                def __init__(self):
                    self.db_path = os.path.join('data', 'crypto_agent.db')
                    os.makedirs(os.path.dirname(self.db_path), exist_ok=True)
                
                @contextmanager
                def get_connection(self, db_type='main'):
                    conn = sqlite3.connect(self.db_path)
                    conn.row_factory = sqlite3.Row
                    try:
                        yield conn
                    finally:
                        conn.close()
            
            db_manager = SimpleDBManager()
    
    @async_timing_decorator
    async def get_exchange_rates(self, base: str, targets: List[str]) -> Dict[str, float]:
        """
        获取汇率数据
        
        Args:
            base: 基础货币代码
            targets: 目标货币代码列表
        
        Returns:
            汇率字典 {currency: rate}
        
        Raises:
            CurrencyConversionError: 汇率获取失败
        """
        try:
            # 验证货币代码
            base = base.upper()
            targets = [t.upper() for t in targets]
            
            if base not in self.supported_currencies:
                raise CurrencyConversionError(f"不支持的基础货币: {base}", from_currency=base)
            
            for target in targets:
                if target not in self.supported_currencies:
                    raise CurrencyConversionError(f"不支持的目标货币: {target}", to_currency=target)
            
            # 检查缓存
            cached_rates = await self._get_cached_rates(base, targets)
            if cached_rates:
                return cached_rates
            
            # 获取新汇率
            fresh_rates = await self._fetch_fresh_rates(base, targets)
            
            # 缓存汇率
            await self._cache_rates(fresh_rates)
            self._rates_cache = fresh_rates
            
            return fresh_rates.rates
            
        except CurrencyConversionError:
            raise
        except Exception as e:
            raise CurrencyConversionError(f"获取汇率失败: {str(e)}", from_currency=base)
    
    @async_timing_decorator
    async def convert_amount(self, amount: float, from_currency: str, to_currency: str) -> float:
        """
        转换金额
        
        Args:
            amount: 金额
            from_currency: 源货币
            to_currency: 目标货币
        
        Returns:
            转换后的金额
        
        Raises:
            CurrencyConversionError: 转换失败
        """
        try:
            from_currency = from_currency.upper()
            to_currency = to_currency.upper()
            
            # 如果是同一货币，直接返回
            if from_currency == to_currency:
                return amount
            
            # 获取汇率
            if from_currency == 'USD':
                # 从USD转换到其他货币
                rates = await self.get_exchange_rates('USD', [to_currency])
                rate = rates.get(to_currency)
                if rate is None:
                    raise CurrencyConversionError(f"无法获取汇率: USD -> {to_currency}", 
                                                from_currency='USD', to_currency=to_currency)
                return amount * rate
            
            elif to_currency == 'USD':
                # 从其他货币转换到USD
                rates = await self.get_exchange_rates('USD', [from_currency])
                rate = rates.get(from_currency)
                if rate is None:
                    raise CurrencyConversionError(f"无法获取汇率: {from_currency} -> USD", 
                                                from_currency=from_currency, to_currency='USD')
                return amount / rate
            
            else:
                # 通过USD作为中介货币转换
                # 先转换到USD
                usd_amount = await self.convert_amount(amount, from_currency, 'USD')
                # 再从USD转换到目标货币
                return await self.convert_amount(usd_amount, 'USD', to_currency)
                
        except CurrencyConversionError:
            raise
        except Exception as e:
            raise CurrencyConversionError(f"货币转换失败: {str(e)}", 
                                        from_currency=from_currency, to_currency=to_currency)
    
    async def convert_multi_currency(self, amounts: Dict[str, float], target_currency: str) -> Dict[str, float]:
        """
        批量货币转换，支持优雅降级
        
        Args:
            amounts: 金额字典 {currency: amount}
            target_currency: 目标货币
        
        Returns:
            转换后的金额字典 {currency: converted_amount}
        """
        result = {}
        conversion_errors = []
        
        for currency, amount in amounts.items():
            try:
                converted_amount = await self.convert_amount(amount, currency, target_currency)
                result[currency] = converted_amount
            except CurrencyConversionError as e:
                conversion_errors.append(f"{currency} -> {target_currency}")
                logger.warning(f"转换失败 {currency} -> {target_currency}: {e}")
                
                # 多级fallback策略
                fallback_success = False
                
                # 1. 尝试fallback到USD
                if currency != 'USD' and target_currency != 'USD':
                    try:
                        usd_amount = await self.convert_amount(amount, currency, 'USD')
                        result[currency] = usd_amount
                        fallback_success = True
                        logger.info(f"使用USD作为fallback: {currency} -> USD")
                    except Exception as fallback_error:
                        logger.warning(f"USD fallback也失败: {fallback_error}")
                
                # 2. 使用固定汇率估算
                if not fallback_success:
                    try:
                        estimated_amount = self._estimate_conversion(amount, currency, target_currency)
                        result[currency] = estimated_amount
                        fallback_success = True
                        logger.info(f"使用估算汇率: {currency} -> {target_currency}")
                    except Exception as estimate_error:
                        logger.warning(f"估算转换也失败: {estimate_error}")
                
                # 3. 最后的fallback - 返回原始金额
                if not fallback_success:
                    result[currency] = amount
                    logger.warning(f"所有转换方法都失败，返回原始金额: {currency}")
        
        # 记录转换错误统计
        if conversion_errors:
            logger.warning(f"货币转换部分失败: {', '.join(conversion_errors)}")
        
        return result
    
    def _estimate_conversion(self, amount: float, from_currency: str, to_currency: str) -> float:
        """
        使用固定汇率估算转换
        
        Args:
            amount: 金额
            from_currency: 源货币
            to_currency: 目标货币
        
        Returns:
            估算的转换金额
        """
        # 固定汇率表（相对于USD）
        fixed_rates = {
            'USD': 1.0,
            'EUR': 0.85,  # 1 USD = 0.85 EUR
            'CNY': 7.2    # 1 USD = 7.2 CNY
        }
        
        from_rate = fixed_rates.get(from_currency.upper(), 1.0)
        to_rate = fixed_rates.get(to_currency.upper(), 1.0)
        
        # 通过USD作为中介货币计算
        usd_amount = amount / from_rate
        converted_amount = usd_amount * to_rate
        
        return converted_amount
    
    def get_supported_currencies(self) -> List[str]:
        """获取支持的货币列表"""
        return self.supported_currencies.copy()
    
    async def _get_cached_rates(self, base: str, targets: List[str]) -> Optional[Dict[str, float]]:
        """获取缓存的汇率"""
        # 检查内存缓存
        if (self._rates_cache and 
            self._rates_cache.base_currency == base and
            not self._rates_cache.is_expired(self.cache_ttl_minutes)):
            
            rates = {}
            for target in targets:
                rate = self._rates_cache.get_rate(target)
                if rate is not None:
                    rates[target] = rate
            
            if len(rates) == len(targets):
                return rates
        
        # 检查数据库缓存
        db_rates = await self._get_rates_from_db(base, targets)
        if db_rates:
            return db_rates
        
        return None
    
    async def _get_rates_from_db(self, base: str, targets: List[str]) -> Optional[Dict[str, float]]:
        """从数据库获取汇率"""
        try:
            # 计算缓存过期时间
            cutoff_time = datetime.now() - timedelta(minutes=self.cache_ttl_minutes)
            cutoff_timestamp = int(cutoff_time.timestamp())
            
            rates = {}
            
            with db_manager.get_connection('main') as conn:
                cursor = conn.cursor()
                
                for target in targets:
                    cursor.execute('''
                        SELECT rate FROM exchange_rates 
                        WHERE base_currency = ? AND target_currency = ? AND timestamp > ?
                        ORDER BY timestamp DESC 
                        LIMIT 1
                    ''', (base, target, cutoff_timestamp))
                    
                    row = cursor.fetchone()
                    if row:
                        rates[target] = row['rate']
            
            # 只有当所有目标货币都有缓存时才返回
            if len(rates) == len(targets):
                return rates
            
            return None
            
        except Exception as e:
            logger.error(f"从数据库获取汇率失败: {e}")
            return None
    
    async def _fetch_fresh_rates(self, base: str, targets: List[str]) -> CurrencyRates:
        """获取新的汇率数据"""
        try:
            # 延迟初始化汇率数据源
            if self.rate_source is None:
                self.rate_source = CoinGeckoMarketSource()
            
            # 使用CoinGecko获取汇率
            return await self.rate_source.get_exchange_rates(base, targets)
        except Exception as e:
            logger.error(f"从外部API获取汇率失败: {e}")
            # 使用fallback汇率
            return await self._get_fallback_rates(base, targets)
    
    async def _get_fallback_rates(self, base: str, targets: List[str]) -> CurrencyRates:
        """获取fallback汇率（固定汇率）"""
        logger.warning(f"使用fallback汇率，基础货币: {base}")
        
        # 固定汇率（相对于USD）
        fixed_rates = {
            'USD': 1.0,
            'EUR': 0.85,  # 1 USD = 0.85 EUR
            'CNY': 7.2    # 1 USD = 7.2 CNY
        }
        
        rates = {}
        
        if base == 'USD':
            for target in targets:
                if target in fixed_rates:
                    rates[target] = fixed_rates[target]
        else:
            # 通过USD计算其他货币的汇率
            base_to_usd = 1.0 / fixed_rates.get(base, 1.0)
            for target in targets:
                if target == 'USD':
                    rates[target] = base_to_usd
                elif target in fixed_rates:
                    rates[target] = base_to_usd * fixed_rates[target]
        
        return CurrencyRates(
            base_currency=base,
            rates=rates,
            timestamp=datetime.now(),
            source='fallback'
        )
    
    async def _cache_rates(self, currency_rates: CurrencyRates):
        """缓存汇率到数据库"""
        try:
            current_time = get_current_timestamp()
            timestamp = int(currency_rates.timestamp.timestamp())
            
            with db_manager.get_connection('main') as conn:
                cursor = conn.cursor()
                
                for target_currency, rate in currency_rates.rates.items():
                    cursor.execute('''
                        INSERT OR REPLACE INTO exchange_rates 
                        (base_currency, target_currency, rate, timestamp, source, created_at)
                        VALUES (?, ?, ?, ?, ?, ?)
                    ''', (
                        currency_rates.base_currency,
                        target_currency,
                        rate,
                        timestamp,
                        currency_rates.source,
                        current_time
                    ))
                
                conn.commit()
                logger.debug(f"缓存汇率成功: {currency_rates.base_currency} -> {list(currency_rates.rates.keys())}")
                
        except Exception as e:
            logger.error(f"缓存汇率失败: {e}")


class CoinGeckoMarketSource(MarketDataSource):
    """CoinGecko市场数据源"""
    
    def __init__(self):
        super().__init__("CoinGecko", "https://api.coingecko.com/api/v3", 50)
        
        # CoinGecko的币种ID映射
        self.coin_id_map = {
            'BTC': 'bitcoin',
            'ETH': 'ethereum',
            'ADA': 'cardano',
            'DOT': 'polkadot',
            'LINK': 'chainlink',
            'LTC': 'litecoin',
            'XRP': 'ripple',
            'BNB': 'binancecoin',
            'SOL': 'solana',
            'MATIC': 'matic-network',
            'AVAX': 'avalanche-2',
            'DOGE': 'dogecoin',
            'SHIB': 'shiba-inu',
            'UNI': 'uniswap',
            'ATOM': 'cosmos'
        }
        
        # 支持的货币
        self.supported_currencies = ['usd', 'eur', 'cny']
    
    @retry_decorator(max_attempts=3, delay=2.0)
    async def get_market_data(self, symbol: str) -> MarketData:
        """从CoinGecko获取市场数据"""
        # 检查缓存
        cache_key = f"market_data_{symbol.upper()}"
        cached_data = self._get_cache(cache_key)
        if cached_data:
            return cached_data
        
        await self._rate_limit_check()
        
        # 获取币种ID
        coin_id = self.coin_id_map.get(symbol.upper())
        if not coin_id:
            raise MarketDataError(f"CoinGecko不支持币种: {symbol}", symbol, self.name)
        
        url = f"{self.base_url}/coins/{coin_id}"
        params = {
            'localization': 'false',
            'tickers': 'false',
            'market_data': 'true',
            'community_data': 'false',
            'developer_data': 'false',
            'sparkline': 'false'
        }
        
        try:
            async with aiohttp.ClientSession() as session:
                async with session.get(url, params=params, timeout=30) as response:
                    if response.status != 200:
                        error_text = await response.text()
                        raise MarketDataError(
                            f"CoinGecko API错误 {response.status}: {error_text}",
                            symbol, self.name
                        )
                    
                    data = await response.json()
                    market_data = self._parse_market_data(data, symbol)
                    
                    # 缓存数据
                    self._set_cache(cache_key, market_data)
                    
                    return market_data
                    
        except aiohttp.ClientError as e:
            raise MarketDataError(f"网络请求失败: {str(e)}", symbol, self.name)
        except Exception as e:
            if isinstance(e, MarketDataError):
                raise
            else:
                raise MarketDataError(f"获取市场数据失败: {str(e)}", symbol, self.name)
    
    def _parse_market_data(self, data: Dict[str, Any], symbol: str) -> MarketData:
        """解析CoinGecko API响应数据"""
        try:
            market_data = data.get('market_data', {})
            
            # 提取市场数据
            market_cap_usd = safe_float(market_data.get('market_cap', {}).get('usd', 0))
            market_cap_rank = safe_int(market_data.get('market_cap_rank', 0))
            volume_24h_usd = safe_float(market_data.get('total_volume', {}).get('usd', 0))
            
            # 计算24小时交易量变化百分比
            volume_change_24h = safe_float(
                market_data.get('total_volume_change_percentage_24h', 0)
            )
            
            # 获取最后更新时间
            last_updated_str = market_data.get('last_updated')
            if last_updated_str:
                try:
                    last_updated = datetime.fromisoformat(last_updated_str.replace('Z', '+00:00'))
                except:
                    last_updated = datetime.now()
            else:
                last_updated = datetime.now()
            
            return MarketData(
                symbol=symbol.upper(),
                market_cap_usd=market_cap_usd,
                market_cap_rank=market_cap_rank,
                volume_24h_usd=volume_24h_usd,
                volume_change_24h_percent=volume_change_24h,
                last_updated=last_updated
            )
            
        except Exception as e:
            raise MarketDataError(f"解析市场数据失败: {str(e)}", symbol, self.name)
    
    @retry_decorator(max_attempts=3, delay=2.0)
    async def get_exchange_rates(self, base_currency: str, target_currencies: List[str]) -> CurrencyRates:
        """从CoinGecko获取汇率数据"""
        # 检查缓存
        cache_key = f"exchange_rates_{base_currency}_{'_'.join(sorted(target_currencies))}"
        cached_data = self._get_cache(cache_key)
        if cached_data:
            return cached_data
        
        await self._rate_limit_check()
        
        # CoinGecko汇率API
        url = f"{self.base_url}/exchange_rates"
        
        try:
            async with aiohttp.ClientSession() as session:
                async with session.get(url, timeout=30) as response:
                    if response.status != 200:
                        error_text = await response.text()
                        raise MarketDataError(
                            f"CoinGecko汇率API错误 {response.status}: {error_text}",
                            source=self.name
                        )
                    
                    data = await response.json()
                    currency_rates = self._parse_exchange_rates(data, base_currency, target_currencies)
                    
                    # 缓存数据
                    self._set_cache(cache_key, currency_rates)
                    
                    return currency_rates
                    
        except aiohttp.ClientError as e:
            raise MarketDataError(f"获取汇率数据网络请求失败: {str(e)}", source=self.name)
        except Exception as e:
            if isinstance(e, MarketDataError):
                raise
            else:
                raise MarketDataError(f"获取汇率数据失败: {str(e)}", source=self.name)
    
    def _parse_exchange_rates(self, data: Dict[str, Any], base_currency: str, target_currencies: List[str]) -> CurrencyRates:
        """解析CoinGecko汇率API响应数据"""
        try:
            rates_data = data.get('rates', {})
            
            # 获取基础货币的BTC汇率
            base_currency_lower = base_currency.lower()
            base_btc_rate = None
            
            if base_currency_lower in rates_data:
                base_btc_rate = safe_float(rates_data[base_currency_lower].get('value', 0))
            elif base_currency_lower == 'btc':
                base_btc_rate = 1.0
            
            if not base_btc_rate or base_btc_rate == 0:
                raise MarketDataError(f"无法获取基础货币 {base_currency} 的汇率", source=self.name)
            
            # 计算目标货币相对于基础货币的汇率
            rates = {}
            for target_currency in target_currencies:
                target_currency_lower = target_currency.lower()
                
                if target_currency_lower == base_currency_lower:
                    rates[target_currency.upper()] = 1.0
                elif target_currency_lower in rates_data:
                    target_btc_rate = safe_float(rates_data[target_currency_lower].get('value', 0))
                    if target_btc_rate > 0:
                        # 通过BTC作为中介计算汇率
                        rate = target_btc_rate / base_btc_rate
                        rates[target_currency.upper()] = rate
                    else:
                        logger.warning(f"无法获取货币 {target_currency} 的有效汇率")
                else:
                    logger.warning(f"CoinGecko不支持货币: {target_currency}")
            
            return CurrencyRates(
                base_currency=base_currency.upper(),
                rates=rates,
                timestamp=datetime.now(),
                source=self.name
            )
            
        except Exception as e:
            if isinstance(e, MarketDataError):
                raise
            else:
                raise MarketDataError(f"解析汇率数据失败: {str(e)}", source=self.name)
    
    def get_supported_symbols(self) -> List[str]:
        """获取支持的币种列表"""
        return list(self.coin_id_map.keys())
    
    def get_supported_currencies(self) -> List[str]:
        """获取支持的货币列表"""
        return self.supported_currencies.copy()


class MarketDataManager:
    """市场数据管理器"""
    
    def __init__(self):
        """初始化市场数据管理器"""
        self.cache_ttl_minutes = 5
        self._market_data_cache: Dict[str, MarketData] = {}
        self._currency_rates_cache: Optional[CurrencyRates] = None
        self.supported_currencies = ['USD', 'EUR', 'CNY']
        
        # 初始化市场数据源
        self.market_source = CoinGeckoMarketSource()
        
        # 初始化数据库管理器
        self._init_db_manager()
        
        # 初始化数据库表
        self._init_database_tables()
    
    def _init_db_manager(self):
        """初始化数据库管理器"""
        global db_manager
        if db_manager is None:
            # 使用简单的数据库管理器用于测试
            import sqlite3
            import os
            from contextlib import contextmanager
            
            class SimpleDBManager:
                def __init__(self):
                    self.db_path = os.path.join('data', 'crypto_agent.db')
                    os.makedirs(os.path.dirname(self.db_path), exist_ok=True)
                
                @contextmanager
                def get_connection(self, db_type='main'):
                    conn = sqlite3.connect(self.db_path)
                    conn.row_factory = sqlite3.Row
                    try:
                        yield conn
                    finally:
                        conn.close()
            
            db_manager = SimpleDBManager()
            logger.info(f"使用简单数据库管理器: {type(db_manager)}")
    
    def _init_database_tables(self):
        """初始化数据库表结构"""
        try:
            with db_manager.get_connection('main') as conn:
                cursor = conn.cursor()
                
                # 创建市场数据表
                cursor.execute('''
                    CREATE TABLE IF NOT EXISTS market_data (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        symbol TEXT NOT NULL,
                        market_cap_usd REAL NOT NULL,
                        market_cap_rank INTEGER,
                        volume_24h_usd REAL NOT NULL,
                        volume_change_24h_percent REAL,
                        timestamp INTEGER NOT NULL,
                        created_at INTEGER NOT NULL,
                        UNIQUE(symbol, timestamp)
                    )
                ''')
                
                # 创建汇率表
                cursor.execute('''
                    CREATE TABLE IF NOT EXISTS exchange_rates (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        base_currency TEXT NOT NULL,
                        target_currency TEXT NOT NULL,
                        rate REAL NOT NULL,
                        timestamp INTEGER NOT NULL,
                        source TEXT NOT NULL,
                        created_at INTEGER NOT NULL,
                        UNIQUE(base_currency, target_currency, timestamp)
                    )
                ''')
                
                # 创建索引
                cursor.execute('CREATE INDEX IF NOT EXISTS idx_market_data_symbol_time ON market_data(symbol, timestamp)')
                cursor.execute('CREATE INDEX IF NOT EXISTS idx_exchange_rates_base_target ON exchange_rates(base_currency, target_currency)')
                cursor.execute('CREATE INDEX IF NOT EXISTS idx_exchange_rates_timestamp ON exchange_rates(timestamp)')
                
                conn.commit()
                logger.info("市场数据数据库表初始化完成")
                
        except Exception as e:
            logger.error(f"初始化市场数据数据库表失败: {e}")
            raise MarketDataError(f"数据库初始化失败: {str(e)}")
    
    @async_timing_decorator
    async def get_market_data(self, symbol: str, currencies: List[str] = None) -> Dict[str, Any]:
        """
        获取市场数据，支持优雅降级
        
        Args:
            symbol: 币种代码
            currencies: 目标货币列表，默认为 ['USD', 'EUR', 'CNY']
        
        Returns:
            包含市场数据和多货币转换的字典，失败时返回部分数据或空数据
        """
        if currencies is None:
            currencies = self.supported_currencies
        
        result = {
            'symbol': symbol,
            'market_cap': None,
            'volume_24h': None,
            'volume_change_24h': None,
            'market_cap_rank': None,
            'last_updated': None,
            'currency_rates': None,
            'error': None,
            'fallback_used': False
        }
        
        try:
            # 尝试获取基础市场数据
            market_data = None
            try:
                market_data = await self._get_cached_market_data(symbol)
                logger.debug(f"成功获取市场数据: {symbol}")
            except MarketDataError as e:
                logger.warning(f"获取市场数据失败，尝试使用缓存数据: {e}")
                # 尝试从数据库获取最近的缓存数据
                market_data = await self._get_fallback_market_data(symbol)
                if market_data:
                    result['fallback_used'] = True
                    logger.info(f"使用缓存的市场数据: {symbol}")
                else:
                    logger.error(f"无法获取任何市场数据: {symbol}")
                    result['error'] = f"市场数据暂时不可用: {str(e)}"
                    return result
            
            # 尝试获取汇率数据
            rates = {}
            try:
                rates = await self._get_exchange_rates('USD', currencies)
                logger.debug(f"成功获取汇率数据: {currencies}")
            except Exception as e:
                logger.warning(f"获取汇率失败，使用fallback汇率: {e}")
                # 使用fallback汇率
                rates = await self._get_fallback_exchange_rates(currencies)
                result['fallback_used'] = True
            
            # 转换为多货币格式
            if market_data and rates:
                try:
                    multi_currency_data = market_data.to_multi_currency(rates)
                    
                    result.update({
                        'market_cap': multi_currency_data['market_cap'],
                        'volume_24h': multi_currency_data['volume_24h'],
                        'volume_change_24h': market_data.volume_change_24h_percent,
                        'market_cap_rank': market_data.market_cap_rank,
                        'last_updated': market_data.last_updated.isoformat(),
                        'currency_rates': {
                            'base': 'USD',
                            'rates': rates,
                            'timestamp': datetime.now().isoformat()
                        }
                    })
                except Exception as e:
                    logger.warning(f"多货币转换失败: {e}")
                    result['error'] = f"货币转换失败: {str(e)}"
                    
                    logger.info(f"市场数据获取成功: {symbol}")
                    
                except Exception as e:
                    logger.error(f"数据转换失败: {e}")
                    result['error'] = f"数据处理失败: {str(e)}"
            
            return result
            
        except Exception as e:
            logger.error(f"获取市场数据时发生未知错误: {e}")
            result['error'] = f"市场数据服务异常: {str(e)}"
            return result
    
    async def _get_cached_market_data(self, symbol: str) -> MarketData:
        """获取缓存的市场数据"""
        # 检查内存缓存
        if symbol in self._market_data_cache:
            cached_data = self._market_data_cache[symbol]
            if not self._is_market_data_expired(cached_data):
                return cached_data
        
        # 检查数据库缓存
        db_data = await self._get_market_data_from_db(symbol)
        if db_data and not self._is_market_data_expired(db_data):
            self._market_data_cache[symbol] = db_data
            return db_data
        
        # 获取新数据（这里暂时返回模拟数据，实际实现会在后续任务中添加）
        fresh_data = await self._fetch_fresh_market_data(symbol)
        
        # 缓存数据
        await self._cache_market_data(fresh_data)
        self._market_data_cache[symbol] = fresh_data
        
        return fresh_data
    
    async def _get_market_data_from_db(self, symbol: str) -> Optional[MarketData]:
        """从数据库获取市场数据"""
        try:
            with db_manager.get_connection('main') as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    SELECT symbol, market_cap_usd, market_cap_rank, volume_24h_usd, 
                           volume_change_24h_percent, timestamp
                    FROM market_data 
                    WHERE symbol = ? 
                    ORDER BY timestamp DESC 
                    LIMIT 1
                ''', (symbol,))
                
                row = cursor.fetchone()
                if row:
                    return MarketData(
                        symbol=row['symbol'],
                        market_cap_usd=row['market_cap_usd'],
                        market_cap_rank=row['market_cap_rank'],
                        volume_24h_usd=row['volume_24h_usd'],
                        volume_change_24h_percent=row['volume_change_24h_percent'],
                        last_updated=datetime.fromtimestamp(row['timestamp'])
                    )
                
                return None
                
        except Exception as e:
            logger.error(f"从数据库获取市场数据失败: {e}")
            return None
    
    async def _get_fallback_market_data(self, symbol: str) -> Optional[MarketData]:
        """获取fallback市场数据（从数据库获取最近的数据）"""
        try:
            # 获取最近30分钟内的数据
            cutoff_time = datetime.now() - timedelta(minutes=30)
            cutoff_timestamp = int(cutoff_time.timestamp())
            
            with db_manager.get_connection('main') as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    SELECT symbol, market_cap_usd, market_cap_rank, volume_24h_usd, 
                           volume_change_24h_percent, timestamp
                    FROM market_data 
                    WHERE symbol = ? AND timestamp > ?
                    ORDER BY timestamp DESC 
                    LIMIT 1
                ''', (symbol.upper(), cutoff_timestamp))
                
                row = cursor.fetchone()
                if row:
                    return MarketData(
                        symbol=row['symbol'],
                        market_cap_usd=row['market_cap_usd'],
                        market_cap_rank=row['market_cap_rank'],
                        volume_24h_usd=row['volume_24h_usd'],
                        volume_change_24h_percent=row['volume_change_24h_percent'],
                        last_updated=datetime.fromtimestamp(row['timestamp'])
                    )
            
            logger.warning(f"没有找到最近的市场数据缓存: {symbol}")
            return None
            
        except Exception as e:
            logger.error(f"获取fallback市场数据失败: {e}")
            return None
    
    async def _get_fallback_exchange_rates(self, currencies: List[str]) -> Dict[str, float]:
        """获取fallback汇率（固定汇率）"""
        logger.warning("使用固定汇率作为fallback")
        
        # 固定汇率（相对于USD）
        fixed_rates = {
            'USD': 1.0,
            'EUR': 0.85,  # 1 USD = 0.85 EUR
            'CNY': 7.2    # 1 USD = 7.2 CNY
        }
        
        rates = {}
        for currency in currencies:
            currency_upper = currency.upper()
            if currency_upper in fixed_rates:
                rates[currency_upper] = fixed_rates[currency_upper]
            else:
                logger.warning(f"不支持的货币: {currency}")
                rates[currency_upper] = 1.0  # 默认汇率
        
        return rates
    
    async def _cache_market_data(self, market_data: MarketData):
        """缓存市场数据到数据库"""
        try:
            current_time = get_current_timestamp()
            
            with db_manager.get_connection('main') as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    INSERT OR REPLACE INTO market_data 
                    (symbol, market_cap_usd, market_cap_rank, volume_24h_usd, 
                     volume_change_24h_percent, timestamp, created_at)
                    VALUES (?, ?, ?, ?, ?, ?, ?)
                ''', (
                    market_data.symbol,
                    market_data.market_cap_usd,
                    market_data.market_cap_rank,
                    market_data.volume_24h_usd,
                    market_data.volume_change_24h_percent,
                    int(market_data.last_updated.timestamp()),
                    current_time
                ))
                conn.commit()
                
        except Exception as e:
            logger.error(f"缓存市场数据失败: {e}")
    
    def _is_market_data_expired(self, market_data: MarketData) -> bool:
        """检查市场数据是否过期"""
        expiry_time = market_data.last_updated + timedelta(minutes=self.cache_ttl_minutes)
        return datetime.now() > expiry_time
    
    async def _fetch_fresh_market_data(self, symbol: str) -> MarketData:
        """从CoinGecko获取新的市场数据"""
        try:
            return await self.market_source.get_market_data(symbol)
        except MarketDataError as e:
            logger.error(f"从CoinGecko获取市场数据失败: {e}")
            # 如果CoinGecko失败，返回模拟数据作为fallback
            return await self._get_fallback_market_data(symbol)
        except Exception as e:
            logger.error(f"获取市场数据时发生未知错误: {e}")
            return await self._get_fallback_market_data(symbol)
    
    async def _get_fallback_market_data(self, symbol: str) -> MarketData:
        """获取fallback市场数据（模拟数据）"""
        import random
        
        logger.warning(f"使用fallback数据为币种: {symbol}")
        
        # 模拟市场数据
        base_market_cap = {
            'BTC': 800_000_000_000,  # 8000亿美元
            'ETH': 300_000_000_000,  # 3000亿美元
            'ADA': 15_000_000_000,   # 150亿美元
        }.get(symbol.upper(), 10_000_000_000)  # 默认100亿美元
        
        market_cap = float(base_market_cap * random.uniform(0.9, 1.1))
        volume = float(market_cap * random.uniform(0.05, 0.15))  # 5-15%的市值作为交易量
        
        return MarketData(
            symbol=symbol.upper(),
            market_cap_usd=market_cap,
            market_cap_rank=random.randint(1, 100),
            volume_24h_usd=volume,
            volume_change_24h_percent=float(random.uniform(-20, 20)),
            last_updated=datetime.now()
        )
    
    async def _get_exchange_rates(self, base_currency: str, target_currencies: List[str]) -> Dict[str, float]:
        """获取汇率数据"""
        # 检查缓存
        if (self._currency_rates_cache and 
            self._currency_rates_cache.base_currency == base_currency and
            not self._currency_rates_cache.is_expired(self.cache_ttl_minutes)):
            
            rates = {}
            for currency in target_currencies:
                rate = self._currency_rates_cache.get_rate(currency)
                if rate is not None:
                    rates[currency] = rate
            
            if len(rates) == len(target_currencies):
                return rates
        
        # 获取新汇率数据
        fresh_rates = await self._fetch_fresh_exchange_rates(base_currency, target_currencies)
        
        # 缓存汇率数据
        await self._cache_exchange_rates(fresh_rates)
        self._currency_rates_cache = fresh_rates
        
        return fresh_rates.rates
    
    async def _fetch_fresh_exchange_rates(self, base_currency: str, target_currencies: List[str]) -> CurrencyRates:
        """从CoinGecko获取新的汇率数据"""
        try:
            return await self.market_source.get_exchange_rates(base_currency, target_currencies)
        except MarketDataError as e:
            logger.error(f"从CoinGecko获取汇率数据失败: {e}")
            # 如果CoinGecko失败，返回模拟数据作为fallback
            return await self._get_fallback_exchange_rates(base_currency, target_currencies)
        except Exception as e:
            logger.error(f"获取汇率数据时发生未知错误: {e}")
            return await self._get_fallback_exchange_rates(base_currency, target_currencies)
    
    async def _get_fallback_exchange_rates(self, base_currency: str, target_currencies: List[str]) -> CurrencyRates:
        """获取fallback汇率数据（模拟数据）"""
        logger.warning(f"使用fallback汇率数据，基础货币: {base_currency}")
        
        rates = {}
        
        # 模拟汇率（相对于USD）
        mock_rates = {
            'USD': 1.0,
            'EUR': 0.85,
            'CNY': 7.2
        }
        
        for currency in target_currencies:
            if currency in mock_rates:
                rates[currency] = mock_rates[currency]
        
        return CurrencyRates(
            base_currency=base_currency,
            rates=rates,
            timestamp=datetime.now(),
            source='fallback'
        )
    
    async def _cache_exchange_rates(self, currency_rates: CurrencyRates):
        """缓存汇率数据到数据库"""
        try:
            current_time = get_current_timestamp()
            timestamp = int(currency_rates.timestamp.timestamp())
            
            with db_manager.get_connection('main') as conn:
                cursor = conn.cursor()
                
                for target_currency, rate in currency_rates.rates.items():
                    cursor.execute('''
                        INSERT OR REPLACE INTO exchange_rates 
                        (base_currency, target_currency, rate, timestamp, source, created_at)
                        VALUES (?, ?, ?, ?, ?, ?)
                    ''', (
                        currency_rates.base_currency,
                        target_currency,
                        rate,
                        timestamp,
                        currency_rates.source,
                        current_time
                    ))
                
                conn.commit()
                
        except Exception as e:
            logger.error(f"缓存汇率数据失败: {e}")
    
    async def get_volume_data(self, symbol: str, timeframe: str = '24h') -> Dict[str, Any]:
        """
        获取交易量数据
        
        Args:
            symbol: 币种代码
            timeframe: 时间框架，默认24h
        
        Returns:
            交易量数据字典
        """
        try:
            market_data = await self._get_cached_market_data(symbol)
            
            return {
                'symbol': symbol,
                'volume_24h_usd': market_data.volume_24h_usd,
                'volume_change_24h_percent': market_data.volume_change_24h_percent,
                'timeframe': timeframe,
                'last_updated': market_data.last_updated.isoformat()
            }
            
        except Exception as e:
            raise MarketDataError(f"获取交易量数据失败: {str(e)}", symbol=symbol)
    
    async def get_market_cap_rank(self, symbol: str) -> int:
        """
        获取市值排名
        
        Args:
            symbol: 币种代码
        
        Returns:
            市值排名
        """
        try:
            market_data = await self._get_cached_market_data(symbol)
            return market_data.market_cap_rank
            
        except Exception as e:
            raise MarketDataError(f"获取市值排名失败: {str(e)}", symbol=symbol)
    
    def get_supported_currencies(self) -> List[str]:
        """获取支持的货币列表"""
        return self.supported_currencies.copy()
    
    async def cleanup_old_data(self, days: int = 7):
        """清理旧的市场数据"""
        try:
            cutoff_time = get_current_timestamp() - (days * 24 * 3600)
            
            with db_manager.get_connection('main') as conn:
                cursor = conn.cursor()
                
                # 清理旧的市场数据
                cursor.execute('DELETE FROM market_data WHERE timestamp < ?', (cutoff_time,))
                market_deleted = cursor.rowcount
                
                # 清理旧的汇率数据
                cursor.execute('DELETE FROM exchange_rates WHERE timestamp < ?', (cutoff_time,))
                rates_deleted = cursor.rowcount
                
                conn.commit()
                
                logger.info(f"清理完成: 市场数据 {market_deleted} 条, 汇率数据 {rates_deleted} 条")
                
                return {
                    'market_data_deleted': market_deleted,
                    'exchange_rates_deleted': rates_deleted
                }
                
        except Exception as e:
            logger.error(f"清理旧数据失败: {e}")
            raise MarketDataError(f"数据清理失败: {str(e)}")


# 全局市场数据管理器实例
market_data_manager = MarketDataManager()

# 全局货币转换器实例
currency_converter = CurrencyConverter()

# 导出
__all__ = [
    'MarketDataManager',
    'MarketData',
    'CurrencyRates', 
    'MarketDataError',
    'CurrencyConversionError',
    'MarketDataSource',
    'CoinGeckoMarketSource',
    'CurrencyConverter',
    'market_data_manager',
    'currency_converter'
]